package com.example.demo.repository;

import com.example.demo.entity.SchoolCourse;
import org.springframework.data.r2dbc.repository.Modifying;
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.r2dbc.repository.R2dbcRepository;
import org.springframework.data.repository.query.Param;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

public interface SchoolCourseRepository extends R2dbcRepository<SchoolCourse, Long> {

    @Query("SELECT * FROM t_course WHERE student_id =:student_id order by id")
    Flux<SchoolCourse> queryAllByStudentId(Long student_id);

    @Query("delete FROM t_course WHERE student_id =:student_id")
    Mono<Void> deleteAllByStudentId(Long student_id);

    @Query("delete from t_course WHERE student_id in (select id from t_student where school_class = :class_id)")
    Mono<Void> deleteAllByClassId(Long class_id);

    @Modifying
    @Query( "insert into t_course(id,name,student_id) VALUES (:#{#schoolCourse.id},:#{#schoolCourse.name},:#{#schoolCourse.student_id})"+
            "on conflict (id) do update SET " +
            "name = CASE WHEN :#{#schoolCourse.name} IS NULL THEN t_course.name ELSE :#{#schoolCourse.name} END, " +
            "student_id = CASE WHEN :#{#schoolCourse.student_id} IS NULL THEN t_course.student_id ELSE :#{#schoolCourse.student_id} END " )
    Mono<SchoolCourse> insertOrUpdateCourse(@Param("schoolCourse") SchoolCourse schoolCourse);

}

